package kael.aiur.database;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public abstract class DBUtil {
	private static Properties properties = new Properties();
	private static DBUtil db = null;
	private static Connection conn = null;
	static {
		InputStream is = null;
		try {
			is = DBUtil.class.getClassLoader().getResourceAsStream(
					"choosefile.properties");
			properties.load(is);
			is.close();
			is = DBUtil.class.getClassLoader().getResourceAsStream(
					properties.getProperty("propertiesfile")+".properties");
			properties.load(is);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			Class.forName(properties.getProperty("DRIVER"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static DBUtil createDBUtil() {
		if (db == null) {
			try {
				Constructor<?> c = Class.forName(
						properties.getProperty("DBUTIL")).getConstructor();
				db = (DBUtil) c.newInstance();
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}
		}
		return db;
	}

	public Connection getConn(String database) {
		if (conn == null) {
			try {
				conn = DriverManager.getConnection(
						properties.getProperty("URL") + (this.urlConnString()==null?"":this.urlConnString())
								+ database, properties.getProperty("USER"),
						properties.getProperty("PASSWORD"));
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
			}
		}
		return conn;
	}

	public Connection getConn() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(properties.getProperty("URL")
					+ (this.urlConnString()==null?"":this.urlConnString())
					+ properties.getProperty("DATABASE"),
					properties.getProperty("USER"),
					properties.getProperty("PASSWORD"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		}
		return conn;
	}

	public PreparedStatement getPstmt(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}
	
	public PreparedStatement getPstmt(String sql) {
		this.getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	public PreparedStatement excPstmt(PreparedStatement pstmt, Object[] value) {
		try {
			for (int i = 0; i < value.length; i++) {
				pstmt.setObject(i, value[i]);
			}
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	public Statement getStmt(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	public Statement getStmt() {
		conn = this.getConn();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}
	
	public Statement excUpdateStmt(Statement stmt, String sql) {
		try {
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	public ResultSet excQueryStmt(Statement stmt, String sql) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public void closeResource(Connection conn, PreparedStatement pstmt,
			Statement stmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Map<String, Object>> toList(ResultSet rs) {
		List<String> columns = this.getColumns(rs);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < columns.size(); i++) {
					map.put(columns.get(i), rs.getObject(i + 1));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public String databaseProductName() {
		if(conn == null){
			conn = this.getConn();
		}
		try {
			String productName = conn.getMetaData().getDatabaseProductName();
			return productName;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public Integer execUpdateSql(String sql){
		Statement stmt = this.getStmt();
		Integer n = null;
		try {
			n = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.closeResource(null, null, stmt, null);
		}
		return n;
	}

	public abstract List<String> getColumns(ResultSet rs);
	protected abstract String urlConnString();

}
